package com.auxgroup.hr.front.service.comm.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.slf4j.helpers.FormattingTuple;
import org.slf4j.helpers.MessageFormatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.domain.Example;
import org.springframework.stereotype.Service;

import com.auxgroup.hr.front.commons.Constant;
import com.auxgroup.hr.front.commons.utils.SpringUtil;
import com.auxgroup.hr.front.dao.domain.PsBankEcTbl;
import com.auxgroup.hr.front.dao.domain.PsCPrcTypeVw;
import com.auxgroup.hr.front.dao.domain.PsCountryTbl;
import com.auxgroup.hr.front.dao.domain.PsEthnicGrpTbl;
import com.auxgroup.hr.front.dao.domain.PsNidType1Vw;
import com.auxgroup.hr.front.dao.domain.PsRegRegionTbl;
import com.auxgroup.hr.front.dao.domain.PsRsActDtlTbl;
import com.auxgroup.hr.front.dao.domain.Psxlatitem;
import com.auxgroup.hr.front.dao.repository.PsCPrcTypeVwRepository;
import com.auxgroup.hr.front.dao.repository.PsRsActDtlTblRepository;
import com.auxgroup.hr.front.dao.repository.PsxlatitemRepository;
import com.auxgroup.hr.front.service.comm.SysDictService;
import com.auxgroup.hr.front.service.personnal.BankEcTblService;
import com.auxgroup.hr.front.service.personnal.CountryTblService;
import com.auxgroup.hr.front.service.personnal.EthnicGrpTblService;
import com.auxgroup.hr.front.service.personnal.NidType1VwService;
import com.auxgroup.hr.front.service.personnal.RegRegionTblService;
import org.springframework.util.CollectionUtils;

/**
 * @author lvliuhao
 * @version 创建时间：180523
 * 入职人员银行账号service
 */
@SuppressWarnings("unchecked")
@Service("sysDictService")
public class SysDictServiceImpl implements SysDictService {

    @Autowired
    private PsRsActDtlTblRepository PsRsActDtlTblRepository;

    @Autowired
    private PsCPrcTypeVwRepository psCPrcTypeVwRepository;

    @Autowired
    private NidType1VwService nidType1VwService;

    @Autowired
    private BankEcTblService bankEcTblService;

    @Autowired
    private CountryTblService countryTblService;

    @Autowired
    private EthnicGrpTblService ethnicGrpTblService;

    @Autowired
    private RegRegionTblService regRegionTblService;

    /* (non-Javadoc)
     * @see SysDictService#getSysDict(java.lang.String)
     */
    @Override
    @Cacheable(value = "DICT_SINGLE_VALUE", key = "#fileName+#fileValue")
    public Psxlatitem getSysDict(String fileName, String fileValue) {
        List<Object[]> items = getObjectListFromSql(Constant.DICT_QUERY_BYVALUE_SQL, new String[]{fileName, fileValue});
        Psxlatitem item = new Psxlatitem();
        if (!items.isEmpty()) {
            Object[] str = items.get(0);
            item.setFieldvalue(String.valueOf(str[0]));
            item.setXlatlongname(String.valueOf(str[1]));
            item.setXlatshortname(String.valueOf(str[2]));
        }
        return item;
    }

    /* (non-Javadoc)
     * @see SysDictService#getSysDict(java.lang.String)
     */
    @Override
    @Cacheable(value = "DICT_LIST_VALUE", key = "#fileName")
    public List<Psxlatitem> getSysDict(String fileName) {
        List<Object[]> items = getObjectListFromSql(Constant.DICT_QUERY_BYNAME_SQL, new String[]{fileName});
        List<Psxlatitem> resultItems = new ArrayList<Psxlatitem>();
        Psxlatitem item = null;
        if (!items.isEmpty()) {
            for (Object[] str : items) {
                item = new Psxlatitem();
                item.setFieldvalue(String.valueOf(str[0]));
                item.setXlatlongname(String.valueOf(str[1]));
                item.setXlatshortname(String.valueOf(str[2]));
                resultItems.add(item);
            }
        }
        return resultItems;
    }

    /* (non-Javadoc)
     * @see SysDictService#getJobDesc(java.lang.String, java.lang.String)
     */
    @Override
    public String getJobDesc(String setId, String jobCode) {
        List<Object[]> items = getObjectListFromSql(Constant.JOBDESC_QUERY_BY_SETID_JOBCODE_SQL, new String[]{setId, jobCode});
        if (!items.isEmpty()) {
            Object obj = items.get(0);
            return String.valueOf(obj);
        }
        return null;
    }

    @Override
    public String getJobPropertyDescshort(String jobType) {
        List<String> costCtr = getStringFromSql(Constant.QUERY_JOB_TYPE_DESCSHORT_SQL, new String[]{jobType});
        if (costCtr != null && !costCtr.isEmpty()) {
            return costCtr.get(0);
        }
        return "";
    }


    @Override
    public String getWfTitle(String cTransactionNbr) {
        List<Object> titles = getObjectListFromSql(Constant.QUERY_WF_TITLE, new String[]{cTransactionNbr}, String.class);
        if (!titles.isEmpty()) {
            Object obj = titles.get(0);
            return String.valueOf(obj);
        }
        return "审批流程";
    }

    /* (non-Javadoc)
     * @see SysDictService#getJobListBySetid(java.lang.String)
     */
    @Override
    public List<Object[]> getJobListBySetid(String setID) {
        return getObjectListFromSql(Constant.JOBLIST_QUERY_BY_SETID_SQL, new String[]{setID});
    }


    @Override
    public List<Object[]> getObjectListFromSql(String querySql, Object[] args) {
        EntityManagerFactory entityManFactory = (EntityManagerFactory) SpringUtil.getBean("entityManagerFactory");
        EntityManager manager = entityManFactory.createEntityManager();
        FormattingTuple ft = MessageFormatter.arrayFormat(querySql, args);
        Query query = manager.createNativeQuery(ft.getMessage());
        List<Object[]> result = query.getResultList();
        if (manager.isOpen()) {
            manager.close();
        }
        return result;
    }

    @Override
    public List<String> getStringFromSql(String querySql, Object[] args) {
        EntityManagerFactory entityManFactory = (EntityManagerFactory) SpringUtil.getBean("entityManagerFactory");
        EntityManager manager = entityManFactory.createEntityManager();
        FormattingTuple ft = MessageFormatter.arrayFormat(querySql, args);
        Query query = manager.createNativeQuery(ft.getMessage());
        List<String> result = query.getResultList();
        if (manager.isOpen()) {
            manager.close();
        }
        return result;
    }


    @Override
    public List<Object> getObjectListFromSql(String querySql, Object[] args, Class<?> entity) {
        EntityManagerFactory entityManFactory = (EntityManagerFactory) SpringUtil.getBean("entityManagerFactory");
        EntityManager manager = entityManFactory.createEntityManager();
        FormattingTuple ft = MessageFormatter.arrayFormat(querySql, args);
        Query query = manager.createNativeQuery(ft.getMessage(), entity);
        List<Object> result = query.getResultList();
        if (manager.isOpen()) {
            manager.close();
        }
        return result;
    }


    @Override
    public List<Object[]> getActionResons(String action) {
        return getObjectListFromSql(Constant.ACTION_RESON_QUERY_SQL, new String[]{action});
    }

    /* (non-Javadoc)
     * @see SysDictService#getTerDetails(java.lang.String)
     */
    @Override
    public List<PsRsActDtlTbl> getTerDetails(String actionReason) {
        PsRsActDtlTbl query = new PsRsActDtlTbl();
        query.setAction("TER");
        query.setActionReason(actionReason);
        return PsRsActDtlTblRepository.findAll(Example.of(query));
    }

    /* (non-Javadoc)
     * @see SysDictService#getSingleTableDict(java.lang.String, java.lang.String)
     */
    @Override
    public String getSingleTableDict(String dictType, String dictKey) {
        switch (dictType) {
            case "nationalIdType":
                PsNidType1Vw type = nidType1VwService.getNidType1Vw(dictKey);
                if (type != null) {
                    return type.getDescr();
                }
                return null;
            case "bankCd":
                PsBankEcTbl bankec = bankEcTblService.getBankEcTbl(dictKey);
                if (bankec != null) {
                    return bankec.getBankNm();
                }
                return null;
            case "country":
                PsCountryTbl country = countryTblService.getCountryTbl(dictKey);
                if (country != null) {
                    return country.getDescr();
                }
                return null;
            case "ethnicGrpCd":
                PsEthnicGrpTbl ethnic = ethnicGrpTblService.getEthnicGrpTbl(dictKey);
                if (ethnic != null) {
                    return ethnic.getDescr50();
                }
                return null;
            case "regRegion":
                PsRegRegionTbl reg = regRegionTblService.getRegRegionTbl(dictKey);
                if (reg != null) {
                    return reg.getDescr50();
                }
                return null;
            case "cPrcType":
                PsCPrcTypeVw prcType = getPrcType(dictKey);
                if (prcType != null) {
                    return prcType.getDescr();
                }
                return null;
            default:
                return null;
        }
    }

    @Override
    public List<PsCPrcTypeVw> getPrcTypes() {
        return psCPrcTypeVwRepository.findAll();
    }


    private PsCPrcTypeVw getPrcType(String type) {
        return psCPrcTypeVwRepository.findOne(type);
    }

    /* (non-Javadoc)
     * @see SysDictService#getPayCompany(java.lang.String)
     */
    @Override
    public String getPayCompany(String company) {
        List<String> companyName = getStringFromSql(Constant.PAY_COMPANY_QUERY_SQL, new String[]{company});
        if (companyName != null && !companyName.isEmpty()) {
            return companyName.get(0);
        }
        return "";
    }

    @Override
    public String getPygrp(String pygroup) {
        List<String> descrs = getStringFromSql(Constant.PYGRP_VW_QUERY_SQL, new String[]{pygroup});
        return CollectionUtils.isEmpty(descrs) ? "" : descrs.get(0);
    }

    @Override
    public String getSilDescr(String silCode) {
        List<String> descrs = getStringFromSql(Constant.SIL_LOC_QUERY_SQL, new String[]{silCode});
        return CollectionUtils.isEmpty(descrs) ? "" : descrs.get(0);
    }

    public String getDeptDescr(String deptId) {
        List<String> descrs = getStringFromSql(Constant.PS_DEPT_QUERY_SQL, new String[]{deptId});
        return CollectionUtils.isEmpty(descrs) ? "" : descrs.get(0);

    }

    /* (non-Javadoc)
     * @see SysDictService#getSiCompany(java.lang.String)
     */
    @Override
    public String getSiCompany(String company) {
        List<String> companyName = getStringFromSql(Constant.SI_COMPANY_QUERY_SQL, new String[]{company, company});
        if (companyName != null && !companyName.isEmpty()) {
            return companyName.get(0);
        }
        return "";
    }

    /* (non-Javadoc)
     * @see SysDictService#getCostCtr(java.lang.String)
     */
    @Override
    public String getCostCtr(String costCenter) {
        List<String> costCtr = getStringFromSql(Constant.COST_CENTER_QUERY_SQL, new String[]{costCenter});
        if (costCtr != null && !costCtr.isEmpty()) {
            return costCtr.get(0);
        }
        return "";
    }

    /* (non-Javadoc)
     * @see SysDictService#getDetpFullName(java.lang.String, java.lang.String)
     */
    @Override
    public String getDetpFullName(String setId, String deptId) {
        List<String> costCtr = getStringFromSql(Constant.QUERY_DEPT_FULL_NAME_SQL, new String[]{setId, deptId});
        if (costCtr != null && !costCtr.isEmpty()) {
            return costCtr.get(0);
        }
        return "";
    }

    @Override
    public String getBusinessName(String businessUnit) {
        List<String> costCtr = getStringFromSql(Constant.QUERY_BUSINESS_NAME_SQL, new String[]{businessUnit});
        if (costCtr != null && !costCtr.isEmpty()) {
            return costCtr.get(0);
        }
        return "";
    }

    @Override
    public String getJobJointDesc(String emplid) {
        List<String> costCtr = getStringFromSql(Constant.QUERY_JOB_JOINT_DESC_SQL, new String[]{emplid});
        if (costCtr != null && !costCtr.isEmpty()) {
            return costCtr.get(0);
        }
        return "";
    }

    @Override
    public String getUnitManagerName(String setidJobcode) {
        List<String> managers = getStringFromSql(Constant.UNIT_MANAGER, new String[]{setidJobcode});
        if (managers != null && !managers.isEmpty()) {
            return managers.get(0);
        }
        return "";
    }

    @Override
    public String getDeptManagerName(String deptid) {
        List<String> managers = getStringFromSql(Constant.DEPT_MANAGER, new String[]{deptid});
        if (managers != null && !managers.isEmpty()) {
            return managers.get(0);
        }
        return "";
    }

    @Override
    public String getLeaveLeftValid(String leaveType) {
        List<String> managers = getStringFromSql(Constant.LEAVE_CHECK_LEFT_SQL, new String[]{leaveType});
        if (managers != null && !managers.isEmpty()) {
            return managers.get(0);
        }
        return "";
    }

    @Override
    public String jointFqyySql(String processType, Boolean isAll) {

        String processName = Constant.processMap.get(processType);

        StringBuilder sb = new StringBuilder("SELECT TO_CHAR(COUNT(*)) FROM  ");
        sb.append(processName);
        sb.append(" A ,PS_C_FQYY_SUB B WHERE A.C_WF_STATUS = 'A' AND A.C_TRANSACTION_NBR = B.C_TRANSACTION_NBR");
        if (!isAll) {
            sb.append(" AND B.C_FQYY_FLAG = 'N'");
        }
        return sb.toString();
    }

    @Override
    public Integer getNoFqyySum(String processType) {
        List<String> managers = getStringFromSql(jointFqyySql(processType, false), new String[]{});
        if (managers != null && !managers.isEmpty()) {
            return Integer.valueOf(managers.get(0));
        }
        return null;
    }

    @Override
    public Integer getProcessTotalSum(String processType) {
        List<String> managers = getStringFromSql(jointFqyySql(processType, true), new String[]{});
        if (managers != null && !managers.isEmpty()) {
            return Integer.valueOf(managers.get(0));
        }
        return null;
    }

    @Override
    public List<String> getFqyyInfo(String cTransactionNbr) {
        List<String> fqyyInfo = getStringFromSql(Constant.QUERY_FQYY_INFO_SQL, new String[]{cTransactionNbr});
        return fqyyInfo;
    }

    @Override
    public List<String> getAuxSystemInfo(String badge) {
        List<String> auxSystemInfoList = getStringFromSql(Constant.QUERY_AUX_SYSTEM_INFO_SQL, new String[]{badge});
        return auxSystemInfoList;
    }

    @Override
    public Double getProcessingOverTimeDays(String emplId) {
        List<String> managers = getStringFromSql("select TO_CHAR(SUM(A.C_OVER_TIME_DAY)) from Ps_c_Wf_Wkot_Hdr A,PS_C_WF_WKOT_DTL B where A.C_TRANSACTION_NBR = B.C_TRANSACTION_NBR AND A.c_wf_status = 'P'  AND B.EMPLID = '" + emplId + "'", new String[]{});
        if (managers != null && !managers.isEmpty()) {
            if (!StringUtils.isEmpty(managers.get(0))) {
                return Double.valueOf(managers.get(0));
            }
        }
        return null;
    }

    @Override
    public String getBuByAccount(String acc) {
        List<String> managers = getStringFromSql("select b.business_unit from ps_c_personal_data a ,ps_job_curr_vw b where a.emplid = b.emplid and b.empl_rcd = 0 and a.c_account = '{}'", new String[]{acc});
        if (managers != null && !managers.isEmpty()) {
            return managers.get(0);
        }
        return "";
    }
}
